import pandas as pd
from copy import deepcopy
import re
from Extend.SqlHelper import SqlServer


class ExcelRead:
    def __init__(self, file_path, sheet_page=0, sheet_name=None):
        if sheet_name:
            self._sheet = pd.read_excel(file_path, sheet_name)
        else:
            self._sheet = pd.read_excel(file_path, sheet_page)
        self._result_dicts = []

    # 读取文件，内容，需要有数据开始行，和需要的列，需要的列以字典形式呈现，方便结果的展示
    def readContent(self, value_column, content_row=0, subsection=None, end_word=None):
        data_frame = pd.DataFrame(self._sheet)
        loop = 0
        sub_list = []
        for x in data_frame.values:
            loop += 1
            if loop < content_row: continue
            s = str(x[0])
            if subsection is not None and str(x[0]).startswith(subsection):
                self._result_dicts.append(deepcopy(sub_list))
                sub_list = []
                continue
            elif end_word is not None and str(x[0]).startswith(end_word):
                break
            res = self.toNewDictionary(x, value_column)
            if res is not None:
                sub_list.append(res)
        if len(sub_list) > 0:
            self._result_dicts.append(deepcopy(sub_list))

    # 将符合条件的数据加入字典当中
    def toNewDictionary(self, item, kwargs):
        result = {}
        for x, y in kwargs.items():
            result[x] = item[y] if str(item[y]) != 'nan' else None
        for x in result.values():
            if x is not None:
                break
        else:
            return None
        return result

    # 用户返会列表
    @property
    def result(self):
        return self._result_dicts


if __name__ == '__main__':

    file = r'E:/K013、K007、K014板材订单处理情况.xlsx'
    items = {'contractno': 0}
    color = 'K014'
    ex = ExcelRead(file, sheet_name=color)
    ex.readContent(items, content_row=1)
    sql_link = SqlServer('172.16.200.5', 'sa', 'Liweijia456', 'EasyFAS_Lwj_Release')
    loop = 2
    for con in ex.result[0]:
        sql = "select count(*) ct from C_OrderGoodsDetail where OrderId in (select id from" \
              " C_Order where ContractNo='{}') and Color like '%{}%' and ([Length] > 2400 or Width >2400)"
        res = sql_link.getOneData(sql.format(con['contractno'], color))
        if res['ct'] > 0:
            print(con['contractno'],loop)
        loop += 1
